package com.mmy.dao.plane;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.plane.TPlaneOrder;

public interface TPlaneOrderRepository extends JpaRepository<TPlaneOrder, Integer> {

	/**
	 * 获取最大流水号
	 * 
	 * @return 返回值说明 流水号
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "SELECT MAX(iID)  FROM tPlaneOrder", nativeQuery = true)
	Object getMaxID();

	/**
	 * 获得订单最大编码
	 *
	 * @author 创建人dingxd
	 * @date 修改日期 2020.11.17
	 */
	@Query(value = "SELECT MAX(cOrderID) FROM tPlaneOrder WHERE LEFT(cOrderID,16) = CONCAT(:iPlanID,REPLACE(DATE(NOW()),'-',''))", nativeQuery = true)
	Object getMaxCode(@Param("iPlanID") String strPlanID);

	/**
	 * 创建
	 * 
	 * @return 返回值说明 流水号
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.10.29 修改时间2021.03.17
	 */
	@Transactional
	@Modifying
	@Query(value = "INSERT INTO tPlaneOrder(iID, cOrderID, iReserveUserID, dReserveDate, cReserveName, cReserveTel, iOrderUserID, dOrderDate, cOrderName, cOrderTel, iOrderState, fOrderAmount, fReceivable, fReceipt, fFloor, fPeer, fPrice, iPayType, cVoucher, iCashierState, dCashierDate, iCashierID, iAccountState, dAccountDate, iAccountID, iInvoiceState, fRefund, iRefundState, iRefundProcess, bRemittanceState, dCreateDate, cCreator, dModifyDate, cReviser, cMemo, cSpare1, cSpare2, cSpare3, cSpare4, cSpare5) VALUES (:id, NULL, 0, NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, NULL, 0, 0, NULL, 0, 0, NULL, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)", nativeQuery = true)
	void save(@Param("id") int id);

	/**
	 * 根据订单ID查询
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tPlaneOrder where iID = :iID", nativeQuery = true)
	TPlaneOrder findByID(@Param("iID") int iID);

	/*
	 * 根据id删除
	 */
	@Transactional
	@Modifying
	@Query(value = "delete from tPlaneOrder where tPlaneOrder.iID in (:lstID)", nativeQuery = true)
	void delPlaneOrder(@Param("lstID") List<Integer> lstID);

	/**
	 * 个人中心通过登录人获取机票预留
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tPlaneOrder where iReserveUserID=?1 and iOrderState=0 order by dReserveDate desc limit ?2,?3", nativeQuery = true)
	List<TPlaneOrder> findByUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取机票订单
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tPlaneOrder where iOrderUserID=?1 and iOrderState in(1,2) order by dOrderDate desc limit ?2,?3", nativeQuery = true)
	List<TPlaneOrder> findByOrderUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取机票退单
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tPlaneOrder where iOrderUserID=?1 and iOrderState =4 order by dOrderDate desc limit ?2,?3", nativeQuery = true)
	List<TPlaneOrder> findByChargeBackUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取机票预留 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tPlaneOrder where iReserveUserID=?1 and iOrderState=0 order by dReserveDate desc", nativeQuery = true)
	int countByUserID(int iUserID);

	/**
	 * 个人中心通过登录人获取机票退单 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tPlaneOrder where iOrderUserID=?1 and iOrderState =4 order by dOrderDate desc", nativeQuery = true)
	int countByRefundUserID(int iUserID);

	/**
	 * 个人中心通过登录人获取机票订单 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tPlaneOrder where iOrderUserID=?1 and iOrderState in(1,2) order by dOrderDate desc", nativeQuery = true)
	int countByOrderUserID(int iUserID);

}
